﻿using System;
using System.Collections.Generic;
using System.ComponentModel.Composition;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using com.nm;
using com.nm.library.Excels;
using com.nm.library.Helper;
using NHibernateVertica;
using VerticaAPI.model;

namespace VerticaAPI.Export
{
    /// <summary>
    /// 导入导出核心码
    /// </summary>
    [Export(typeof(ExcelImport))]
    public class DwSizeType : ExcelImport
    {
        public override ExcelImportType Type => ExcelImportType.DWSIZETYPE;

        public override Dictionary<string, ImportVerify> DictFields => dictFields;

        /// <summary>
        /// Excel字段映射
        /// </summary>
        #region Excel字段映射
        private static Dictionary<string, ImportVerify> dictFields = new List<ImportVerify> {
            new ImportVerify{ ColumnName="城市",FieldName="FullName",DataType=typeof(string),VerifyFunc =(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,50,true,true)},
            new ImportVerify{ ColumnName="城市编号",FieldName="AreaID",DataType=typeof(string),VerifyFunc =(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,20,true,true)},
            new ImportVerify{ ColumnName="类别",FieldName="KindID",DataType=typeof(string),VerifyFunc =(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,20,true,true)},
            new ImportVerify{ ColumnName="性别",FieldName="SexName",DataType=typeof(string),VerifyFunc =(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,20,false,true)},
            new ImportVerify{ ColumnName="版型",FieldName="ClothesVersionCode",DataType=typeof(string),VerifyFunc =(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,20,false,true)},
            new ImportVerify{ ColumnName="尺码",FieldName="SizeName",DataType=typeof(string),VerifyFunc =(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,20,false,true)},
            new ImportVerify{ ColumnName="是否核心码",FieldName="SizeCoreFlag",DataType=typeof(string),VerifyFunc =(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,20,false,true)},
            new ImportVerify{ ColumnName="陈列量",FieldName="DisplayQty",DataType=typeof(string),VerifyFunc =(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,20,false,true)}

            //改版，使用另外一种方式
            //new ImportVerify{ ColumnName="F",FieldName="F",DataType=typeof(int),VerifyFunc =(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="XS",FieldName="XS",DataType=typeof(int),VerifyFunc =(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="XXS",FieldName="XXS",DataType=typeof(int),VerifyFunc =(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="S",FieldName="S",DataType=typeof(int),VerifyFunc =(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="M",FieldName="M",DataType=typeof(int),VerifyFunc =(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="L",FieldName="L",DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true) },
            //new ImportVerify{ ColumnName="XL",FieldName="XL",DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true) },
            //new ImportVerify{ ColumnName="XXL",FieldName="XXL" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="3XL",FieldName="N3XL" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="4XL",FieldName="N4XL" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="JM",FieldName="JM" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="25",FieldName="N25" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="26",FieldName="N26" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="27",FieldName="N27" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="28",FieldName="N28" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="29",FieldName="N29" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="30",FieldName="N30" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="31",FieldName="N31" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="32",FieldName="N32" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="33",FieldName="N33" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="34",FieldName="N34" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="35",FieldName="N35" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="36",FieldName="N36" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="37",FieldName="N37" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="38",FieldName="N38" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="39",FieldName="N39" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="40",FieldName="N40" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="41",FieldName="N41" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="42",FieldName="N42" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="43",FieldName="N43" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="44",FieldName="N44" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)},
            //new ImportVerify{ ColumnName="45",FieldName="N45" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,2,false,true)}
           }.ToDictionary(e => e.ColumnName, e => e);
        #endregion

        /// <summary>
        /// 导入数据
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="extraInfo"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public override object SaveImportData(DataTable dt, Dictionary<string, object> extraInfo, int userId)
        {
            var index = 0;
            DataTable msg = new DataTable();
            var result = new ImportResult
            {
                IsSuccess = true,
                ExtraInfo = "导入成功",
            };
            try
            {
                //数据开头的尺码加"N"字符
                //var regNum = new Regex("^[0-9]");
                //for (var i = 0; i < dt.Columns.Count; i++)
                //{
                //    if (regNum.IsMatch(dt.Columns[i].ColumnName))
                //    {
                //        dt.Columns[i].ColumnName = "N" + dt.Columns[i].ColumnName;
                //    }
                //}
                var handler = new SizeTypeHandler();
                var list = ModelConvertHelper<SizeTypeModel.ImportModel>.ConvertToModel(dt);
                // handler.ChangeType(list,out index);
                handler.ChangeType(list, out msg, out index);
                result.Message = $"成功导入{index}条数据{(dt.Rows.Count - index == 0 ? "" : $@",有{dt.Rows.Count - index}条错误数据")}";
                if (msg.Rows.Count > 0)
                {
                    var path = ExcelImporMapper.GetTemplatePath(Type);
                    result.Url = ExcelUtil.ExportErrorExeclToDB(msg, path, dictFields, "城市核心码", index);
                }
                //handler.ModSizeType(ModelConvertHelper<SizeTypeModel.ListModel>.ConvertToModel(dt));
            }
            catch (Exception ex)
            {
                Log.Error("导入城市核心码：" + ex);
                result.IsSuccess = false;
                result.ExtraInfo = ex;
                result.Message = ex.Message;
            }
            return result;
        }
      
        #region  改版，此版本搭配 城市核心码Old.xls 使用
        /*
        public override object SaveImportData(DataTable dt, Dictionary<string, object> extraInfo, int userId)
        {
            var index = 0;
            DataTable msg = new DataTable();
            var result = new ImportResult
            {
                IsSuccess = true,
                ExtraInfo = "导入成功",
            };
            try
            {
                //数据开头的尺码加"N"字符
                var regNum = new Regex("^[0-9]");
                for (var i = 0; i < dt.Columns.Count; i++)
                {
                    if (regNum.IsMatch(dt.Columns[i].ColumnName))
                    {
                        dt.Columns[i].ColumnName = "N" + dt.Columns[i].ColumnName;
                    }
                }
                var handler = new SizeTypeHandler();
                var list = ModelConvertHelper<SizeTypeModel.SizeModel>.ConvertToModel(dt);
                // handler.ChangeType(list,out index);
                handler.ChangeType(list, out msg, out index);
                result.Message = $"成功导入{index}条数据{(dt.Rows.Count - index == 0 ? "" : $@",有{dt.Rows.Count - index}条错误数据")}";
                if (msg.Rows.Count > 0)
                {
                    var path = ExcelImporMapper.GetTemplatePath(Type);
                    result.Url = ExcelUtil.ExportErrorExeclToDB(msg, path, dictFields, "城市核心码", index);
                }
                //handler.ModSizeType(ModelConvertHelper<SizeTypeModel.ListModel>.ConvertToModel(dt));
            }
            catch (Exception ex)
            {
                Log.Error("导入城市核心码：" + ex);
                result.IsSuccess = false;
                result.ExtraInfo = ex;
                result.Message = ex.Message;
            }
            return result;
        }
        */
        #endregion
        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="Keys"></param>
        /// <returns>DataTable</returns>
        public override DataTable GetExportData(string Keys)
        {
            var helper = new VerticaDataHelper();
            var sqlSize = @"select a.AreaID,a.FullName,b.*,{0} as KindID,'' as SexName
from  ods.ods_erp_Bas_Area a
left join dw.vw_dw_size_type b on a.AreaID=b.AreaID
left join dw.dw_AreaProCity c on c.AreaID=b.AreaID
where a.Level=2 {1}
order by a.AreaID asc";
            if (!string.IsNullOrWhiteSpace(Keys))
            {
                var index = Keys.IndexOf("and KindID = ");
                sqlSize = string.Format(sqlSize, Keys.Substring(index, Keys.Length - index).Replace("and KindID = ", ""), $" {Keys.Substring(0, index - 1)}");
            }
            //if (!string.IsNullOrWhiteSpace(Keys))
            //    sqlSize += $" and a.FullName like '{Keys}'";
            //if (!string.IsNullOrWhiteSpace(ShopArea))
            //    sqlSize += $" and c.ParentAreaID = '{ShopArea}' ";
            //if (!string.IsNullOrWhiteSpace(Province))
            //    sqlSize += $" and a.ParentID = '{Province}' ";
            //if (!string.IsNullOrWhiteSpace(City))1
            //    sqlSize += $" and a.ErpAreaID ='{City}' ";
            //找出所有城市的核心码
            var cmdSize = helper.GetSqlStringCommand(sqlSize);
            var dt = helper.ExecuteDataTable(cmdSize);
            var regNum = new Regex("^[N]");
            for (var i = 0; i < dt.Columns.Count; i++)
            {
                if (regNum.IsMatch(dt.Columns[i].ColumnName))
                {
                    dt.Columns[i].ColumnName = dt.Columns[i].ColumnName.Substring(1, dt.Columns[i].ColumnName.Length - 1);
                }
            }
            return dt;
        }
    }
}
